package dbConnection;
//********************************************************************************************************************
//**  //This checks the database connection string.
//********************************************************************************************************************
import java.*;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import iModel.DBReflexiveModel;

public class SQL2012Connection {

   private java.sql.Connection  con = null;
   private  String url;
   private  String serverName;    //hostname for the database server
   private  String portNumber;    //port number for the database server note not default port
   private  String databaseName;  //the databasse name that we want to connect to
   private  String userName;      //the username that we use to connect to the database
   private  String password;      //the password we use to connect to the database
   private  String selectMethod;  //the selection method
   
   // Constructor
   public SQL2012Connection(String url, String serverName, String portNumber, String databaseName, String userName,String password,String selectMethod)
   {
  	 this.url=url;
  	 this.serverName=serverName;
  	 this.portNumber=portNumber;
  	 this.databaseName=databaseName;
  	 this.userName=userName;
  	 this.password=password;
  	 this.selectMethod=selectMethod; 
   }
   
   public String geturl()
   {
  	return this.url; 
   }
   
   public String getserverName()
   {
  	return this.serverName; 
   }
   
   public String getportNumber()
   {
  	return this.portNumber; 
   }
   
   public String getdatabaseName()
   {
  	return this.databaseName; 
   }
   
   public String getuserName()
   {
  	return this.userName; 
   }
   
   public String getpassword()
   {
  	return this.password; 
   }
   
   public String getselectMethod()
   {
  	return this.selectMethod; 
   }
   

   public void seturl(String url)
   {
   	this.url = url;
   }
   
   public void setserverName(String serverName)
   {
   	this.serverName = serverName;
   }
   
   public void setportNumber(String portNumber)
   {
   	this.portNumber = portNumber;
   }
   
   public void setdatabaseName(String databaseName)
   {
   	this.databaseName = databaseName;
   }
   
   public void setuserName(String userName)
   {
   	this.userName = userName;
   }
   
   public void setpassword(String password)
   {
   	this.userName = password;
   }
   
   public void setselectMethod(String selectMethod)
   {
   	this.selectMethod = selectMethod;
   }
             
   
   //get the connection URL for the database connection.
   private String getConnectionUrl()
   {
        return geturl()+getserverName()+":"+getportNumber()+";databaseName="+getdatabaseName()+";selectMethod="+getselectMethod()+";";
   }
   
   public java.sql.Connection getConnection()
   {
  	//This is the driver for the SQL Server 2012.
        try{
      	  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);     
           }
         catch(Exception e)
            {
             e.printStackTrace();
             System.out.println("Error Trace in getConnection() : " + e.getMessage());
           }   	 
        return con;
    }
   

   /*
        Display the driver properties, database details for test purposes only.
   */ 
   public void displayDbProperties(){
        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try{
             con= this.getConnection();
             if(con!=null){
                  dm = con.getMetaData();
                  System.out.println("Driver Information");
                  System.out.println("\tDriver Name: "+ dm.getDriverName());
                  System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
                  System.out.println("\nDatabase Information ");
                  System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
                  System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                  System.out.println("Avalilable Catalogs ");
                  rs = dm.getCatalogs();
                  while(rs.next()){
                       System.out.println("\tcatalog: "+ rs.getString(1));
                  } 
                  rs.close();
                  rs = null;
                  closeConnection();
             }else System.out.println("Error: There was an issue getting the datbaase property information.");
        }catch(Exception e){
             e.printStackTrace();
        }
        dm=null;
   }     
   
   //Now we need to get a list of all the database tables in our chosen database to add to our model.
   public String[] getSQLTables()
   {
  	 java.sql.DatabaseMetaData md = null;
       java.sql.ResultSet rs = null;
       try
       {
            con= this.getConnection();
            if(con!=null)
            {
          	  md = con.getMetaData();
          	  String[] type = new String[4];
          	  type[0] = "TABLE";  //We are only interested in tables at this point so we filter on TABLES however this will give both system and user tables at present.
				  rs = md.getTables(databaseName, null, "%", type);
				  String[] arrTables = new String[100];   //need to amend this later to include the exact count of tables.
				  int i = 0;
				  while (rs.next())
                 {
                  //May need to look at returning the list of tables as an array here.....
					 arrTables[i] =  rs.getString(1).concat(".").concat(rs.getString(2)).concat(".").concat(rs.getString(3));
              	 //getSQLColumns(rs.getString(1),rs.getString(2),rs.getString(3));
					 i++;
                 }
				  return arrTables;
             }
                 else System.out.println("Error: There were issues retrieving the list of database tables");
        }
        catch(Exception e)
        {
             e.printStackTrace();
        }
      md=null;
		return null;
      
  }      
           
  public ArrayList<String> getSQLColumns(String tablename) 
   {
  	try
  	{
  		 con= this.getConnection();
           if(con!=null)
           {
        	   
              Statement st = con.createStatement();
         	   ResultSet rscols = st.executeQuery("Select TOP 1 * from ".concat(tablename));
         	   ResultSetMetaData mdcols = rscols.getMetaData();
         	 //String[] arrCols = new String[100];
         	ArrayList<String> arrCols = new ArrayList<String>(); 
         	  for (int i = 1; i <= mdcols.getColumnCount(); i++)
         	   {
         		
         	   //arrCols.add = mdcols.getColumnName(i);
         	  arrCols.add(mdcols.getColumnName(i));
         	  
         	   }
         	   
         	   return arrCols;
           }
           else System.out.println("Error: There were problems retrieving the column information");
      }
  	catch(Exception e)
      {
  		System.out.println("Error: There were problems retrieving the column information");
           e.printStackTrace();
      }
		return null;
	}

	private void closeConnection()
   {
        try{
             if(con!=null)
                  con.close();
             con=null;
        }catch(Exception e){
             e.printStackTrace();
        }
   }
	
	public ArrayList<String> getPrimaryKey(String tablename)
	{
		 
		java.sql.DatabaseMetaData md = null;
	       java.sql.ResultSet rs = null;
	       try
	       {
	            con= this.getConnection();
	            if(con!=null)
	            {
	          	  md = con.getMetaData();
	          	  //getPrimaryKeys(catalog, schema, table);
	          	    rs = md.getPrimaryKeys(null, null, tablename);
	          	    //Now we need to check that there is a PK for the table if not then we need to make a composite Primary Key
	          	    ArrayList<String> arrPrimaryKeys = new ArrayList<String>(); 
	          	    //As there is no direct way to get the number of records from the result set we will do the following to check.
	          	    int recordCount = 0;
	          	    while (rs.next()) 
	          	   {
	               String columnName = rs.getString("COLUMN_NAME");
	                //At this point we need to put this into an array list as its possible that we have composite primary keys that we will need to deal with some how.
	                arrPrimaryKeys.add(columnName);
	                recordCount++; //add 1 to the recordCount
	              }
	          	  //Now we need to check that if the recordCount is 0 then no PrimaryKeys have been returned and therefore we need a composite Primary Key to be returned
	          	  if(recordCount==0)
	          	  {
	          		System.out.println("Warning: The table does not have a primary Key column defined we will use a composite PK of all columns.");  
	          		ArrayList<String> arrPrimaryKeysComposite= getSQLColumns(tablename);
	          		return arrPrimaryKeysComposite;
	          	  } 
	          	  else 
	          	  {
	          		return arrPrimaryKeys;
	          	  }
	             }
	                 else System.out.println("Error: There were issues retrieving the list of primark Key from the table");
	        }
	        catch(Exception e)
	        {
	             e.printStackTrace();
	        }
	      md=null;
			return null;
	      
	  }      
		
	
	
	
	public String getcolumnstring(ArrayList<String> arrCols)
	{
		StringBuilder sb = new StringBuilder(); 
		boolean first = true; 
        /* Iterate the column-names */ 
		for (int i=0; i < arrCols.size(); i++) 
		{ 	
			
        sb.append("[".concat(arrCols.get(i)).concat("]"));
		if(i < arrCols.size()-1)
		{
			sb.append(", "); 
		}
		} 
		
		return sb.toString(); 
	}
	  
	
   }


